#!/usr/bin/env python
# -*- coding: utf-8 -*-
# Author      : moshan
# Mail        : mo_shan@yeah.net
# Version     : 1.0
# Created Time: 2021-02-21 10:40:02
# Function    : 主程序
#########################################################################

def f_get_monitor_data_dir_sql(m_size, m_type) :

    sql = """select concat(a.info,':""" + m_type + """') info from (
        (SELECT t1.info,
                (t1.size - t2.size) size
        FROM 
            (SELECT concat(rshost,':',dir) info,
                min(size) size
            FROM tb_monitor_dir_info force index(idx_a_time)
            WHERE a_time > DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 """ + m_type + """), '%Y-%m-%d 23:59:59')
                    AND a_time <= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 0 """ + m_type + """), '%Y-%m-%d 23:59:59')
            GROUP BY  rshost,dir) t1
        LEFT JOIN 
            (SELECT concat(rshost,':',dir) info,
                min(size) size
            FROM tb_monitor_dir_info force index(idx_a_time)
            WHERE a_time > DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 2 """ + m_type + """), '%Y-%m-%d 23:59:59')
                    AND a_time <= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 """ + m_type + """), '%Y-%m-%d 23:59:59')
            GROUP BY  rshost,dir) t2
            ON t1.info = t2.info
        WHERE (t1.size - t2.size) > """ + m_size + """) 
        UNION ALL 
        (SELECT t1.info,
                (t1.size - t2.size) size
        FROM 
            (SELECT concat(rshost,':',dir) info,
                min(size) size
            FROM tb_monitor_dir_info force index(idx_a_time)
            WHERE a_time > DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 2 """ + m_type + """), '%Y-%m-%d 23:59:59')
                    AND a_time <= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 1 """ + m_type + """), '%Y-%m-%d 23:59:59')
            GROUP BY  rshost,dir) t1
        LEFT JOIN 
            (SELECT concat(rshost,':',dir) info,
                min(size) size
            FROM tb_monitor_dir_info force index(idx_a_time)
            WHERE a_time > DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 3 """ + m_type + """), '%Y-%m-%d 23:59:59')
                    AND a_time <= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 2 """ + m_type + """), '%Y-%m-%d 23:59:59')
            GROUP BY  rshost,dir) t2
            ON t1.info = t2.info
        WHERE (t1.size - t2.size) > """ + m_size + """)
        UNION ALL 
        (SELECT t1.info,
                (t1.size - t2.size) size
        FROM 
            (SELECT concat(rshost,':',dir) info,
                min(size) size
            FROM tb_monitor_dir_info force index(idx_a_time)
            WHERE a_time > DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 3 """ + m_type + """), '%Y-%m-%d 23:59:59')
                    AND a_time <= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 2 """ + m_type + """), '%Y-%m-%d 23:59:59')
            GROUP BY  rshost,dir) t1
        LEFT JOIN 
            (SELECT concat(rshost,':',dir) info,
                min(size) size
            FROM tb_monitor_dir_info force index(idx_a_time)
            WHERE a_time > DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 4 """ + m_type + """), '%Y-%m-%d 23:59:59')
                    AND a_time <= DATE_FORMAT( DATE_SUB(CURDATE(), INTERVAL 3 """ + m_type + """), '%Y-%m-%d 23:59:59')
            GROUP BY  rshost,dir) t2
            ON t1.info = t2.info
        WHERE (t1.size - t2.size) > """ + m_size + """)
        )a
        group by a.info having count(*) > 2;
"""

    return sql

def f_get_monitor_host_sql(rule) :

    sql = """
        SELECT concat(tmdi.rshost,
             ':',tmhc.remarks,'_disk_',tmdi.part,':::剩余',JSON_EXTRACT(tmdi.disk_info,"$.free")-0,'GB,使用百分比',
             round((JSON_EXTRACT(tmdi.disk_info,"$.used") - 0)/(JSON_EXTRACT(tmdi.disk_info,"$.total") - 0) * 100,2),'%')
        FROM tb_monitor_disk_info tmdi force index(idx_a_time)
        JOIN tb_monitor_host_config tmhc
            ON tmdi.rshost = tmhc.rshost
        WHERE tmdi.a_time > date_add(now(), INTERVAL - 5 MINUTE)
            AND (
                JSON_EXTRACT(tmdi.disk_info,"$.free") - 0 < 200
                OR (((JSON_EXTRACT(tmdi.disk_info,"$.used") - 0)/(JSON_EXTRACT(tmdi.disk_info,"$.total") - 0)) * 100 > """ + rule["disk"] + """ AND tmhc.remarks not in ('mysqlbackup','tidbnfs'))
                OR (((JSON_EXTRACT(tmdi.disk_info,"$.used") - 0)/(JSON_EXTRACT(tmdi.disk_info,"$.total") - 0)) * 100 > 99 AND tmhc.remarks in ('mysqlbackup','tidbnfs'))
            )
        GROUP BY  tmdi.rshost
        UNION
        SELECT concat(tmdi.rshost,
            ':',tmhc.remarks,'_mem:::剩余',JSON_EXTRACT(tmdi.mem_info,"$.free")-0,'GB,使用百分比',round((JSON_EXTRACT(tmdi.mem_info,"$.used") - 0)/(JSON_EXTRACT(tmdi.mem_info,"$.total") - 0) * 100,2),'%')
        FROM tb_monitor_host_info tmdi force index(idx_a_time)
        JOIN tb_monitor_host_config tmhc
            ON tmdi.rshost = tmhc.rshost
        WHERE tmdi.a_time > date_add(now(), INTERVAL - 5 MINUTE)
            AND ( JSON_EXTRACT(tmdi.mem_info,"$.free") - 0 < 20
            OR ((JSON_EXTRACT(tmdi.mem_info,"$.used") - 0)/(JSON_EXTRACT(tmdi.mem_info,"$.total") - 0)) * 100 > """ + rule["mem"] + """ )
        GROUP BY  tmdi.rshost
        UNION
        SELECT concat(tmdi.rshost,':',tmhc.remarks,'_cpu:::负载',JSON_EXTRACT(tmdi.cpu_info,"$.load")-0,',总数',(JSON_EXTRACT(tmdi.cpu_info,"$.total") - 0))
        FROM tb_monitor_host_info tmdi force index(idx_a_time)
        JOIN tb_monitor_host_config tmhc
            ON tmdi.rshost = tmhc.rshost
        WHERE tmdi.a_time > date_add(now(), INTERVAL - 5 MINUTE) 
            AND (JSON_EXTRACT(tmdi.cpu_info,"$.load") - 0 > JSON_EXTRACT(tmdi.cpu_info,"$.total") - 0 and tmhc.remarks = 'mysql')
        GROUP BY  tmdi.rshost;
"""
    return sql
